USE [comunion]
GO

/****** Object:  View [dbo].[V_PIBalanceInquiry_Voucher]    Script Date: 06/21/2010 17:45:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[V_PIBalanceInquiry_Voucher]
as
SELECT     cCompanyID, cTranNo, ISNULL(SUM(nGross), 0) AS nGross, ISNULL(SUM(nPaid), 0) AS nPaid, ISNULL(SUM(nDebit), 0) AS nDebit, ISNULL(SUM(nCredit), 0) AS nCredit, 
                      ISNULL(SUM(nReturns), 0) AS nReturns
FROM         ((SELECT     A.cCompanyID, A.cTranNo, SUM(A.nGross) AS nGross, 0 AS nPaid, 0 AS nDebit, 0 AS nCredit, 0 AS nReturns
                         FROM         PURCHASE A
                         WHERE     A.lCancelled = 0
                         GROUP BY A.cCompanyID, A.cTranNo)
                      UNION ALL
                      (SELECT     A.cCompanyID, A.cInvNo AS cWRRNo, 0 AS nGross, SUM(A.nApplied) AS nPaid, 0 AS nDebit, 0 AS nCredit, 0 AS nReturns
                       FROM         RFP_TRADE A
                       GROUP BY A.cCompanyID, A.cInvNo)
UNION ALL
(SELECT     A.cCompanyID, A.cPINo, 0 AS nGross, 0 AS nPaid, SUM(A.nDebit) AS nDebit, 0 AS nCredit, 0 AS nReturns
 FROM         AP_T A LEFT OUTER JOIN
                        AP B ON A.cTranNo = B.cTranNo AND A.cCompanyID = B.cCompanyID
 WHERE     B.cType = 'Debit' AND B.lCancelled = 0
 GROUP BY A.cCompanyID, A.cPINo)
UNION ALL
(SELECT     A.cCompanyID, A.cPINo, 0 AS nGross, 0 AS nPaid, 0 AS nDebit, SUM(A.nCredit) AS nCredit, 0 AS nReturns
 FROM         AP_T A LEFT OUTER JOIN
                        AP B ON A.cTranNo = B.cTranNo AND A.cCompanyID = B.cCompanyID
 WHERE     B.cType = 'Credit' AND B.lCancelled = 0
 GROUP BY A.cCompanyID, A.cPINo)) A
GROUP BY cCompanyID, cTranNo
GO


